package net.app.help.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import net.app.help.model.Contact;
import net.app.help.model.Group;
import net.app.help.model.Message;

import java.util.ArrayList;

/**REMOVE DB: adb.exe -e shell rm data/data/net.app.help/databases/HELPBaza
 * PULL DB: adb.exe pull data/data/net.app.help/databases/HELPBaza C:\sqlite3HELPDB.sqlite
 * C:\Program Files\Java\android-studio-sdk\platform-tools>
 * Created by Boogaboo on 18.12.2014.
 *
 * BOLJŠA PRAKSA JE ZA VSAKO TABELO V BAZI USTVARITI DAO RAZRED! takrat ni vse pomešano...
 * smiselno dodati count metodo, lahko imamo custom queryje, getterje za table name..(lol okxD)
 * 
 *
 */
public class DatabaseAdapter {

	public static final String DB_NAME = "HELPBaza";
	public static final int DB_VERSION = 2;
//	CONTACT TABLE
	public static final String DB_TABLE_CONTACTS = "CONTACTS";
	public static final String ID_CON = "id";
	public static final String ID_GRP = "id_grp";
	public static final String ID_MSG = "id_msg";
	public static final String NAME = "name";
	public static final String NUMBER = "number";
	public static final String[] COLUMNS_CONTACTS = {ID_CON, ID_GRP, ID_MSG, NAME, NUMBER};
//	GROUPS TABLE
	public static final String DB_TABLE_GROUPS = "GROUPS";
//	public static final String ID_GRP = "id";
//	public static final String ID_MSG = "id_msg";
//	public static final String NAME = "name";
	public static final String[] COLUMNS_GROUPS = {ID_GRP, ID_MSG, NAME};
//	MESSAGES TABLE
	public static final String DB_TABLE_MESSAGES = "MESSAGES";
//	public static final String ID_MSG = "id";
	public static final String TITLE = "title";
	public static final String CONTENT = "content";
	public static final String[] COLUMNS_MESSAGES = {ID_MSG, TITLE, CONTENT};

	private DatabaseHelper sqLiteHelper;
	private SQLiteDatabase sqLiteDatabase;

	private Context context;

	public static final String SCRIPT_CREATE_TABLE_CONTACTS = "CREATE TABLE IF NOT EXISTS " + DB_TABLE_CONTACTS + " (" +
			ID_CON + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
			ID_GRP + " INTEGER, " +
			ID_MSG + " INTEGER, " +
			NAME + " TEXT, " +
			NUMBER + " TEXT)";

	public static final String SCRIPT_CREATE_TABLE_GROUPS = "CREATE TABLE IF NOT EXISTS " + DB_TABLE_GROUPS + " (" +
			ID_GRP + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
			ID_MSG + " INTEGER, " +
			NAME + " TEXT)";

	public static final String SCRIPT_CREATE_TABLE_MESSAGES = "CREATE TABLE IF NOT EXISTS " + DB_TABLE_MESSAGES + " (" +
			ID_MSG + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
			TITLE + " TEXT, " +
			CONTENT + " TEXT)";

	public static final String SCRIPT_DROP_TABLE_CONTACTS = "DROP TABLE IF EXISTS " + DB_TABLE_CONTACTS;
	public static final String SCRIPT_DROP_TABLE_GROUPS = "DROP TABLE IF EXISTS " + DB_TABLE_GROUPS;
	public static final String SCRIPT_DROP_TABLE_MESSAGES = "DROP TABLE IF EXISTS " + DB_TABLE_MESSAGES;

	public DatabaseAdapter(Context c){
		context = c;
	}

	/**Vsakič ustvarim nov RVIRDatabaseHelper in */
	public DatabaseAdapter openToRead() {
		sqLiteHelper = new DatabaseHelper(context, DB_NAME, null, DB_VERSION);
		sqLiteDatabase = sqLiteHelper.getReadableDatabase();
		return this;
	}

	public DatabaseAdapter openToWrite() {
		sqLiteHelper = new DatabaseHelper(context, DB_NAME, null, DB_VERSION);
		sqLiteDatabase = sqLiteHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		sqLiteHelper.close();
	}

	/*CONTACTS SECTION*/
	public ArrayList<Contact> getAllContacts() {
		ArrayList<Contact> contacts = new ArrayList<Contact>();
		String query = "SELECT * FROM " + DB_TABLE_CONTACTS;
		Cursor cursor = sqLiteDatabase.rawQuery(query, null);
		Log.d("getAllContacts", "getCount="+cursor.getCount());
		Contact c;
		if (cursor.moveToFirst()) {
			do {
				c = new Contact();
				c.setId(cursor.getInt(0));
				c.setId_grp(cursor.getInt(1));
				c.setId_msg(cursor.getInt(2));
				c.setName(cursor.getString(3));
				c.setTel(cursor.getString(4));

				contacts.add(c);
			} while (cursor.moveToNext());
		}
		Log.d("getAllContacts", "contacts.size()="+ contacts.size() +" contacts.toString()="+contacts.toString());
		return contacts;
	}

	public long addContact(Contact c) {
		ContentValues values = new ContentValues();
		values.put(ID_GRP, "0");			//NA ZAČETKU JE ID 0, dokler se ga ne doda v skupino
		values.put(ID_MSG, "0");			//ID = 0, dokler se mu ne pripne sporočila
		values.put(NAME, c.getName());
		values.put(NUMBER, c.getTel());
		long i = sqLiteDatabase.insert(DB_TABLE_CONTACTS, null, values);
		Log.d("addContact", "Contact.toString()" + c.toString()+" new row ID="+i);
		return i;
	}

	/**Deletes a contact from the DB table.*/
	public int deleteContact(Contact c) {
		int i = sqLiteDatabase.delete(DB_TABLE_CONTACTS,
				ID_CON + " = ?",
				new String[]{ String.valueOf( c.getId() ) });
		Log.d("deleteContact", "c.toString()="+c.toString()+" rows affected="+i);
		return i;
	}
	/**Deletes a contact by ID from the DB table*/
	public int deleteContact(int id) {
		int i = sqLiteDatabase.delete(DB_TABLE_CONTACTS,
				ID_CON + " = ?",
				new String[]{ String.valueOf( id ) });
		Log.d("deleteContact", "ID="+id+" rows affected="+i);
		return i;
	}

	/**Adds id_grp to a matching contact*/
	public int addContactInAGroup(Contact c, Group g) {
		ContentValues values = new ContentValues();
		values.put(ID_GRP, g.getId());
		int i = sqLiteDatabase.update(DB_TABLE_CONTACTS,
				values,
				ID_CON + " = ?",
				new String[] { String.valueOf( c.getId() ) });
		Log.d("addContactInAGroup", "c.toString()="+c.toString()+" g.getId()="+g.getId()+" rows affected="+i);
		return i;
	}

	/**Adds id_msg to a matching contact*/
	public int addContactAMessage(Contact c, Message m) {
		ContentValues values = new ContentValues();
		values.put(ID_MSG, m.getId());
		int i = sqLiteDatabase.update(DB_TABLE_CONTACTS,
				values,
				ID_CON + " = ?",
				new String[] { String.valueOf( c.getId() ) });
		Log.d("addContactAMessage", "c.toString()="+c.toString()+" m.getId()="+m.getId()+" rows affected="+i);
		return i;
	}

	public int removeContactFromAGroup(Contact c) {
		ContentValues values = new ContentValues();
		values.put(ID_GRP, 0);
		int i = sqLiteDatabase.update(DB_TABLE_CONTACTS,
				values,
				ID_CON + " = ?",
				new String[] { String.valueOf( c.getId() ) });
		Log.d("removeContactFromAGroup", "c.toString()="+c.toString()+" rows affected="+i);
		return i;
	}

	public int removeContactAMessage(Contact c) {
		ContentValues values = new ContentValues();
		values.put(ID_MSG, 0);
		int i = sqLiteDatabase.update(DB_TABLE_CONTACTS,
				values,
				ID_CON + " = ?",
				new String[] { String.valueOf( c.getId() )});
		Log.d("removeContactAMessage", "c.toString()="+c.toString()+" rows affected="+i);
		return i;
	}

	/*GROUPS SECTION*/
	public ArrayList<Group> getAllGroups() {
		ArrayList<Group> groups = new ArrayList<Group>();
		String query = "SELECT * FROM " + DB_TABLE_GROUPS;
		Cursor cursor = sqLiteDatabase.rawQuery(query, null);
		Log.d("getAllGroups", "getCount="+cursor.getCount());
		Group g;
		if (cursor.moveToFirst()) {
			do {
				g = new Group();
				g.setId(cursor.getInt(0));
				g.setId_msg(cursor.getInt(1));
				g.setName(cursor.getString(2));
				groups.add(g);
			} while (cursor.moveToNext());
		}
		Log.d("getAllGroups", "groups.size()="+ groups.size() +" groups.toString()="+groups.toString());
		return groups;
	}

	public long addGroup(Group g) {
		ContentValues values = new ContentValues();
		values.put(ID_MSG, "0");			//ID = 0, dokler se ji ne pripne sporočila
		values.put(NAME, g.getName());
		long i = sqLiteDatabase.insert(DB_TABLE_GROUPS, null, values);
		Log.d("addGroup", "g.toString()"+g.toString()+" new row ID="+i);
		return i;
	}

	/**Returns the number of rows affected*/
	public int updateGroup(Group g) {
		ContentValues values = new ContentValues();
		values.put(ID_MSG, g.getId_msg());
		values.put(NAME, g.getName());
		int i = sqLiteDatabase.update(DB_TABLE_GROUPS,
				values,
				ID_GRP + " = ?",
				new String[] { String.valueOf(g.getId()) });
		Log.d("updateGroup", "g.toString()=" + g.toString());
		return i;
	}

	/**Deletes a group with matching ID,
	 * passes through all contacts and removes matching grp_id and replaces it with 0.*/
	public int deleteGroup(Group g) {
		//delete group
		int i = sqLiteDatabase.delete(DB_TABLE_GROUPS,
				ID_GRP + " = ?",
				new String[]{ String.valueOf( g.getId() ) });
		Log.d("deleteGroup", "g.toString()="+g.toString()+" rows affected="+i);
		//revert group id back to 0 in all contacts that belonged to that group
		ContentValues values = new ContentValues();
		values.put(ID_GRP, 0);
		int j = sqLiteDatabase.update(DB_TABLE_CONTACTS,
				values,
				ID_GRP + " = ?",
				new String[] { String.valueOf( g.getId() ) });
		Log.d("deleteGroup","j="+j+" -> število revertanih ID_GRP med kontakti");
		return i + j;
	}

	/**Adds id_msg to a Group*/
	public int addGroupAMessage(Group g, Message m) {
		ContentValues values = new ContentValues();
		values.put(ID_MSG, m.getId());
		int i = sqLiteDatabase.update(DB_TABLE_GROUPS,
				values,
				ID_GRP + " = ?",
				new String[] { String.valueOf( g.getId() ) });
		Log.d("addGroupAMessage", "g.toString()="+g.toString()+" m.getId()="+m.getId()+" rows affected="+i);
		return i;
	}

	public int removeGroupAMessage(Group g) {
		ContentValues values = new ContentValues();
		values.put(ID_MSG, 0);
		int i = sqLiteDatabase.update(DB_TABLE_GROUPS,
				values,
				ID_GRP + " = ?",
				new String[] { String.valueOf( g.getId() )});
		Log.d("removeGroupAMessage", "g.toString()="+g.toString()+" rows affected="+i);
		return i;
	}



	/*MESSAGES SECTION*/
	public ArrayList<Message> getAllMessages() {
		ArrayList<Message> messages = new ArrayList<Message>();
		String query = "SELECT * FROM " + DB_TABLE_MESSAGES;
		Cursor cursor = sqLiteDatabase.rawQuery(query, null);
		Log.d("getAllMessages", "getCount="+cursor.getCount());
		Message m;
		if (cursor.moveToFirst()) {
			do {
				m = new Message();
				m.setId(cursor.getInt(0));
				m.setTitle(cursor.getString(1));
				m.setContent(cursor.getString(2));
				messages.add(m);
			} while (cursor.moveToNext());
		}
		Log.d("getAllMessages", "messages.size()="+ messages.size() +" messages.toString()="+messages.toString());
		return messages;
	}

	public long addMessage(Message m) {
		ContentValues values = new ContentValues();
		values.put(TITLE, m.getTitle());
		values.put(CONTENT, m.getContent());
		long i = sqLiteDatabase.insert(DB_TABLE_MESSAGES, null, values);
		Log.d("addMessage", "m.toString()" + m.toString()+" new row ID="+i);
		return i;
	}

	public int updateMessage(Message m) {
		ContentValues values = new ContentValues();
		values.put(TITLE, m.getTitle());
		values.put(CONTENT, m.getContent());
		int i = sqLiteDatabase.update(DB_TABLE_MESSAGES,
				values,
				ID_MSG + " = ?",
				new String[] { String.valueOf(m.getId()) });
		Log.d("updateMessage", "m.toString()="+m.toString());
		return i;
	}

	/**Deletes a message with matching ID,
	 * passes through all contacts and removes matching msg_id and replaces it with 0,
	 * passes through all groups and removes matching msg_id and replaces it with 0.*/
	public int deleteMessage(Message m) {
		//delete message
		int i = sqLiteDatabase.delete(DB_TABLE_MESSAGES,
				ID_MSG + " = ?",
				new String[]{ String.valueOf( m.getId() ) });
		Log.d("deleteMessage", "m.toString()="+m.toString()+" rows affected="+i);
		//revert message id back to 0 in all contacts that had message attached
		ContentValues values = new ContentValues();
		values.put(ID_MSG, 0);
		int j = sqLiteDatabase.update(DB_TABLE_CONTACTS,
				values,
				ID_MSG + " = ?",
				new String[] { String.valueOf( m.getId() ) });
		Log.d("deleteMessage","j="+j+" -> število revertanih ID_MSG med kontakti");
		//revert message id back to 0 in all groups that had message attached
		int k = sqLiteDatabase.update(DB_TABLE_GROUPS,
				values,
				ID_MSG + " = ?",
				new String[] { String.valueOf( m.getId() ) });
		Log.d("deleteMessage","k="+k+" -> število revertanih ID_MSG med skupinami");
		return i + j + k;
	}

}
